package util;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;

/**
 * Excel表读取数据poi
 * @author My
 *
 */
public class ExcelDemo2 {
	
	
	/**
	   * 读取Excel
	   * 
	   * @return 
	 * @throws IOException 
	   */
	public static List<String>  readExcel() throws IOException {
		
		List<String> list=new ArrayList<String>();
		  HSSFWorkbook workbook = null;
		// 读取Excel文件
	      InputStream inputStream = new FileInputStream("c:/test.xls");
	      //获得工作薄
	      workbook = new HSSFWorkbook(inputStream);
	      inputStream.close();
	      
	      //循环工作表
	      for(int numSheet=0;numSheet<workbook.getNumberOfSheets();numSheet++){
	    	 HSSFSheet hssfSheet= workbook.getSheetAt(numSheet);
	    	 
	    	 if (hssfSheet == null) {
	 	        continue;
	 	      }
	    	 
	    	  // 循环行
	    	 for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++){
	    		 HSSFRow hssfRow= hssfSheet.getRow(rowNum);
	    		 
	    		 if (hssfRow == null) {
	   	          continue;
	    		 }
	    		 
	    		 StringBuffer sb=new StringBuffer();
	    		 //循环列
	    		 for(int line=0;line<hssfRow.getLastCellNum();line++){
	    			 String cell = hssfRow.getCell(line).getStringCellValue();
	    			sb.append(cell+",");
	    		 }
	    		 list.add(sb.toString());
	    	 }
	    	 
	      }
	      
	      return list;
	
	  }
	
	@Test
	public void testName() throws Exception {
		
		List<String> r = readExcel();
		
		Iterator<String> it = r.iterator();
		while(it.hasNext()){
			String st = it.next();
			System.out.println(st);
		}
		
	}
	
	
	 /**
	   * 创建Excel
	   * 
	   * @param list
	   *      数据
	   */
	public static void createExcel() {
	   
		// 创建一个Excel文件
		    HSSFWorkbook workbook = new HSSFWorkbook();
		    // 创建一个工作表
		    HSSFSheet sheet = workbook.createSheet("考生信息表");
		    // 添加表头行
		    HSSFRow hssfRow = sheet.createRow(0);
		    // 设置单元格格式居中
		    HSSFCellStyle cellStyle = workbook.createCellStyle();
		    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		 
		    // 添加表头内容
		    HSSFCell headCell = hssfRow.createCell(0);
		    headCell.setCellValue("准考证号");
		    headCell.setCellStyle(cellStyle);
		 
		    headCell = hssfRow.createCell(1);
		    headCell.setCellValue("密码");
		    headCell.setCellStyle(cellStyle);
		 
		  
		  
		      // 创建单元格，并设置值
		      HSSFCell cell = hssfRow.createCell(0);
		      cell.setCellValue("12314");
		      cell.setCellStyle(cellStyle);
		 
		      cell = hssfRow.createCell(1);
		      cell.setCellValue("abc123");
		      cell.setCellStyle(cellStyle);
		 
		    // 保存Excel文件
		    try {
		      OutputStream outputStream = new FileOutputStream("c:/test.xls");
		      workbook.write(outputStream);
		      outputStream.close();
		    } catch (Exception e) {
		      e.printStackTrace();
		    }
	  }

}
